Imports nsDataProvider = KDESolutions.Providers.DataProvider
Imports System.Configuration
Imports System.Security.Cryptography
Imports System.Text

Public Class DBEventProvider
    Inherits EventProvider
    Private _DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString As String
    Private Const cTrueValue As String = "Y"
    Private Const cFalsevalue As String = "N"
    Private _TablePrefix As String

    Public Property MyDBType() As String
        Get
            Return _DBType
        End Get
        Set(ByVal value As String)
            _DBType = value
        End Set
    End Property

    Public Overrides ReadOnly Property Name() As String
        Get
            Return "KDESolutions.Providers.Events.DBEventProvider"
        End Get
    End Property

    Public Overrides ReadOnly Property Description() As String
        Get
            Return "Provider for storing date events"
        End Get
    End Property

    Public Overrides Sub Initialize(ByVal name As String, ByVal config As System.Collections.Specialized.NameValueCollection)
        If (config Is Nothing) Then
            Throw New ArgumentNullException("config")
        End If

        If ((name Is Nothing) OrElse (name.Length = 0)) Then
            name = "ImageProvider"
        End If
        If String.IsNullOrEmpty(config("description")) Then
            config.Remove("description")
            config.Add("description", "KDESolutions Event provider")
        End If
        _TablePrefix = GetCOnfigValue(config("tablePrefix"), "events")

        'Start of config file settings for database connection ******
        If String.IsNullOrEmpty("DataProviderGroup") Then
            Throw New ArgumentNullException("You must provide a DataProviderGroup")
        Else
            Dim DataProviderGroup As KDESolutions.Providers.DataProvider.DataProviderConfiguration = _
            CType(ConfigurationManager.GetSection(config("DataProviderGroup")), KDESolutions.Providers.DataProvider.DataProviderConfiguration)
            If String.IsNullOrEmpty(DataProviderGroup.DBType) = False Then
                _DBType = DataProviderGroup.DBType
                Me.MyDBType = DataProviderGroup.DBType
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBServer) = False Then
                _DBServer = DataProviderGroup.DBServer
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBUser) = False Then
                _DBUser = DataProviderGroup.DBUser
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBPassword) = False Then
                _DBPassword = DataProviderGroup.DBPassword
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBDatabaseName) = False Then
                _DBDatabaseName = DataProviderGroup.DBDatabaseName
            End If
            If String.IsNullOrEmpty(DataProviderGroup.ConnectString) = False Then
                _ConnectString = DataProviderGroup.ConnectString
            End If
        End If
    End Sub

    Public Overrides Sub AddEvent(ByVal EventItem As DataTypes.EventItem, ByVal Username As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sNewEventId As String
        If String.IsNullOrEmpty(EventItem.EventId) Then
            sNewEventId = System.Guid.NewGuid.ToString
        Else
            sNewEventId = EventItem.EventId
        End If
        Dim sSQL As String = "INSERT INTO " & _TablePrefix & "_Events(EventId,EventTitle,EventDescription,EventUrl,EventStartDate,EventEndDate," & _
        "CreatedBy,CreateDate,LastUpdatedBy,LastUpdatedDate,LogicalDeleteInd) VALUES(?,?,?,?,?,?,?,?,?,?,?)"
        DAP.AddParameter("@EventId", sNewEventId, DbType.String)
        DAP.AddParameter("@EventTitle", EventItem.Title, DbType.String)
        DAP.AddParameter("@EventDescription", EventItem.Description, DbType.String)
        DAP.AddParameter("@EventUrl", EventItem.Url, DbType.String)
        DAP.AddParameter("@EventStartDate", EventItem.EventStartDate, DbType.Date)
        DAP.AddParameter("@EventEndDate", EventItem.EventEndDate, DbType.Date)
        DAP.AddParameter("@CreatedBy", Username, DbType.String)
        DAP.AddParameter("@CreatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        DAP.ExecuteNonQuery(sSQL)
        If Not EventItem.EventSchedule Is Nothing AndAlso EventItem.EventSchedule.Count > 0 Then
            For Each Item As DataTypes.EventScheduleItem In EventItem.EventSchedule
                Dim sNewEventItemId As String = System.Guid.NewGuid.ToString
                sSQL = "INSERT INTO " & _TablePrefix & "_EventSchedule(ScheduleId,ScheduleTitle,ScheduleStartDateTime,ScheduleEndDateTime," & _
                "CreatedBy,CreateDate,LastUpdatedBy,LastUpdatedDate,LogicalDeleteInd) VALUES(?,?,?,?,?,?,?,?,?)"
                DAP.AddParameter("@ScheduleId", sNewEventItemId, DbType.String)
                DAP.AddParameter("@ScheduleTitle", Item.ItemTitle, DbType.String)
                DAP.AddParameter("@ScheduleStartDateTime", Item.ItemStartDateTime, DbType.DateTime)
                DAP.AddParameter("@ScheduleEndDateTime", Item.ItemEndDateTime, DbType.DateTime)
                DAP.AddParameter("@CreatedBy", Username, DbType.String)
                DAP.AddParameter("@CreatedDate", Now, DbType.DateTime)
                DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
                DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
                DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
                DAP.ExecuteNonQuery(sSQL)
            Next
        End If
    End Sub

    Public Overrides Sub AddScheduleItem(ByVal EventId As String, ByVal EventScheduleItem As DataTypes.EventScheduleItem, ByVal Username As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL = "INSERT INTO " & _TablePrefix & "_EventSchedule(ScheduleId,ScheduleTitle,ScheduleStartDateTime,ScheduleEndDateTime," & _
        "CreatedBy,CreateDate,LastUpdatedBy,LastUpdatedDate,LogicalDeleteInd) VALUES(?,?,?,?,?,?,?,?,?,?)"
        Dim sNewEventItemId As String = System.Guid.NewGuid.ToString
        DAP.AddParameter("@ScheduleId", sNewEventItemId, DbType.String)
        DAP.AddParameter("@ScheduleTitle", EventScheduleItem.ItemTitle, DbType.String)
        DAP.AddParameter("@ScheduleStartDateTime", EventScheduleItem.ItemStartDateTime, DbType.DateTime)
        DAP.AddParameter("@ScheduleEndDateTime", EventScheduleItem.ItemEndDateTime, DbType.DateTime)
        DAP.AddParameter("@CreatedBy", Username, DbType.String)
        DAP.AddParameter("@CreatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        DAP.ExecuteNonQuery(sSQL)
    End Sub

    Public Overrides Function GetEvent(ByVal EventId As String) As DataTypes.EventItem
        Dim returnEvent As DataTypes.EventItem
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT EventId, EventTitle, EventDescription, EventUrl, EventStartDate, EventEndDate " & _
        "FROM " & _TablePrefix & "_Events WHERE LogicalDeleteInd = ? AND EventId = ?"
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        DAP.AddParameter("@EventId", EventId, DbType.String)
        Dim result As New Generic.List(Of Common.DbDataRecord)
        result = DAP.ExecuteReader(sSQL)
        For Each myReader As Common.DbDataRecord In result
            Dim addEvent As New DataTypes.EventItem(myReader.Item("EventId"), myReader.Item("EventTitle"), _
            myReader.Item("EventDescription"), myReader.Item("EventUrl"), _
            myReader.Item("EventStartDate"), myReader.Item("EventEndDate"))
            returnEvent = addEvent
        Next

        Dim returnEventSchedule As New Generic.List(Of DataTypes.EventScheduleItem)
        sSQL = "SELECT ScheduleId, ScheduleTitle, ScheduleDescription, ScheduleStartDateTime, ScheduleEndDateTime " & _
        "FROM " & _TablePrefix & "_EventSchedule WHERE EventId = ? AND LogicalDeleteInd = ?"
        DAP.AddParameter("@EventId", EventId, DbType.String)
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        Dim result1 As New Generic.List(Of Common.DbDataRecord)
        result1 = DAP.ExecuteReader(sSQL)
        For Each readerSchedule As Common.DbDataRecord In result1
            Dim addSchedule As New DataTypes.EventScheduleItem(readerSchedule.Item("ScheduleId"), _
            readerSchedule.Item("ScheduleTitle"), readerSchedule.Item("ScheduleStartDateTime"), _
            readerSchedule.Item("ScheduleEndDateTime"))
            returnEvent.EventSchedule.Add(addSchedule)
        Next
        Return returnEvent
    End Function

    Public Overloads Overrides Function GetEvents() As System.Collections.Generic.List(Of DataTypes.EventItem)
        Dim returnEvents As New Generic.List(Of DataTypes.EventItem)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT EventId, EventTitle, EventDescription, EventUrl, EventStartDate, EventEndDate " & _
        "FROM " & _TablePrefix & "_Events WHERE LogicalDeleteInd = ?"
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        Dim result As New Generic.List(Of Common.DbDataRecord)
        result = DAP.ExecuteReader(sSQL)
        For Each myReader As Common.DbDataRecord In result
            Dim addEvent As New DataTypes.EventItem(myReader.Item("EventId"), myReader.Item("EventTitle"), myReader.Item("EventDescription"), _
            myReader.Item("EventUrl"), _
            myReader.Item("EventStartDate"), myReader.Item("EventEndDate"))
            returnEvents.Add(addEvent)
        Next

        For Each Item As DataTypes.EventItem In returnEvents
            Dim returnEventSchedule As New Generic.List(Of DataTypes.EventScheduleItem)
            sSQL = "SELECT ScheduleId, ScheduleTitle, ScheduleDescription, ScheduleStartDateTime, ScheduleEndDateTime " & _
            "FROM " & _TablePrefix & "_EventSchedule WHERE EventId = ? AND LogicalDeleteInd = ?"
            DAP.AddParameter("@EventId", Item.EventId, DbType.String)
            DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
            Dim result1 As New Generic.List(Of Common.DbDataRecord)
            result1 = DAP.ExecuteReader(sSQL)
            For Each readerSchedule As Common.DbDataRecord In result1
                Dim addSchedule As New DataTypes.EventScheduleItem(readerSchedule.Item("ScheduleId"), _
                readerSchedule.Item("ScheduleTitle"), readerSchedule.Item("ScheduleStartDateTime"), _
                readerSchedule.Item("ScheduleEndDateTime"))
                Item.EventSchedule.Add(addSchedule)
            Next
        Next
        Return returnEvents
    End Function

    Public Overloads Overrides Function GetEvents(ByVal RangeStart As Date, ByVal RangeEnd As Date) As System.Collections.Generic.List(Of DataTypes.EventItem)
        Dim returnEvents As New Generic.List(Of DataTypes.EventItem)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "SELECT EventId, EventTitle, EventDescription, EventUrl, EventStartDate, EventEndDate " & _
        "FROM " & _TablePrefix & "_Events WHERE LogicalDeleteInd = ? AND EventStartDate >= ? AND EventEndDate <= ?"
        DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
        DAP.AddParameter("@EventStartDate", RangeStart, DbType.Date)
        DAP.AddParameter("@EventEndDate", RangeEnd, DbType.Date)
        Dim result1 As New Generic.List(Of Common.DbDataRecord)
        result1 = DAP.ExecuteReader(sSQL)
        For Each myReader As Common.DbDataRecord In result1
            Dim addEvent As New DataTypes.EventItem(myReader.Item("EventId"), myReader.Item("EventTitle"), myReader.Item("EventDescription"), _
            myReader.Item("EventUrl"), myReader.Item("EventStartDate"), myReader.Item("EventEndDate"))
            returnEvents.Add(addEvent)
        Next

        For Each Item As DataTypes.EventItem In returnEvents
            Dim returnEventSchedule As New Generic.List(Of DataTypes.EventScheduleItem)
            sSQL = "SELECT ScheduleId, ScheduleTitle, ScheduleDescription, ScheduleStartDateTime, ScheduleEndDateTime " & _
            "FROM " & _TablePrefix & "_EventSchedule WHERE EventId = ? AND LogicalDeleteInd = ?"
            DAP.AddParameter("@EventId", Item.EventId, DbType.String)
            DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
            Dim result As New Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            For Each readerSchedule As Common.DbDataRecord In result
                Dim addSchedule As New DataTypes.EventScheduleItem(readerSchedule.Item("ScheduleId"), _
                readerSchedule.Item("ScheduleTitle"), readerSchedule.Item("ScheduleStartDateTime"), _
                readerSchedule.Item("ScheduleEndDateTime"))
                Item.EventSchedule.Add(addSchedule)
            Next
        Next
        Return returnEvents
    End Function

    Public Overrides Function GetSchedule(ByVal EventId As String) As System.Collections.Generic.List(Of DataTypes.EventScheduleItem)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String
    End Function

    Public Overrides Sub RemoveEvent(ByVal EventId As String, ByVal Username As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & _TablePrefix & "_Events SET LogicalDeleteInd = ?, LastUpdatedDate = ?, LastUpdatedBy = ? WHERE EventId = ?"
        DAP.AddParameter("@LogicalDeleteInd", cTrueValue, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
        DAP.AddParameter("@EventId", EventId, DbType.String)
        DAP.ExecuteNonQuery(sSQL)

        sSQL = "UPDATE " & _TablePrefix & "_EventSchedule SET LogicalDeleteInd = ?, LastUpdatedDate = ?, LastUpdatedBy = ? WHERE EventId = ?"
        DAP.AddParameter("@LogicalDeleteInd", cTrueValue, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
        DAP.AddParameter("@EventId", EventId, DbType.String)
        DAP.ExecuteNonQuery(sSQL)
    End Sub

    Public Overrides Sub RemoveScheduleItem(ByVal EventScheduleId As String, ByVal Username As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String
        sSQL = "UPDATE " & _TablePrefix & "_EventSchedule SET LogicalDeleteInd = ?, LastUpdatedDate = ?, LastUpdatedBy = ? WHERE EventScheduleId = ?"
        DAP.AddParameter("@LogicalDeleteInd", cTrueValue, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
        DAP.AddParameter("@EventScheduleId", EventScheduleId, DbType.String)
        DAP.ExecuteNonQuery(sSQL)
    End Sub

    Public Overrides Sub UpdateEvent(ByVal EventItem As DataTypes.EventItem, ByVal Username As String)
        Dim returnEvents As New Generic.List(Of DataTypes.EventItem)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & _TablePrefix & "_Events SET EventTitle = ?, EventUrl = ?, EventDescription =?, EventStartDate = ?, " & _
        "EventEndDate = ?, LastUpdatedBy = ?, LastUpdatedDate = ? WHERE EventId = ?"
        DAP.AddParameter("@EventTitle", EventItem.Title, DbType.String)
        DAP.AddParameter("@EventUrl", EventItem.Url, DbType.String)
        DAP.AddParameter("@EventDescription", EventItem.Description, DbType.String)
        DAP.AddParameter("@EventStartDate", EventItem.EventStartDate, DbType.Date)
        DAP.AddParameter("@EventEndDate", EventItem.EventEndDate, DbType.Date)
        DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@EventId", EventItem.EventId, DbType.String)
        DAP.ExecuteNonQuery(sSQL)
        If Not EventItem Is Nothing AndAlso Not EventItem.EventSchedule Is Nothing AndAlso EventItem.EventSchedule.Count > 0 Then
            For Each Item As DataTypes.EventScheduleItem In EventItem.EventSchedule
                sSQL = "UPDATE " & _TablePrefix & "_EventSchedule SET ScheduleDescription = ?, ScheduleStartDateTime = ?, " & _
                "ScheduleEndDateTime = ?, ScheduleTitle = ?, LastUpdatedBy =?, LastUpdatedDate = ? WHERE EventId = ? AND ScheduleID = ?"
                DAP.AddParameter("@EventId", EventItem.EventId, DbType.String)
                DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
                DAP.AddParameter("@ScheduleStartDateTime", EventItem.EventStartDate, DbType.DateTime)
                DAP.AddParameter("@ScheduleEndDateTime", EventItem.EventEndDate, DbType.DateTime)
                DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
                DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
                DAP.AddParameter("@EventId", EventItem.EventId, DbType.String)
                UpdateScheduleItem(EventItem.EventId, Item, Username)
            Next
        End If
    End Sub

    Public Overrides Sub UpdateScheduleItem(ByVal EventId As String, ByVal EventScheduleItem As DataTypes.EventScheduleItem, ByVal Username As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim sSQL As String = "UPDATE " & _TablePrefix & "_EventSchedule ScheduleDescription =?, ScheduleTitle = ?, ScheduleStartDateTime = ?, " & _
        "ScheduleEndDateTime =?, LastUpdatedBy = ?, LastUpdatedDate = ? WHERE EventId = ? AND ScheduleId = ?"
        DAP.AddParameter("@ScheduleTitle", EventScheduleItem.ItemTitle, DbType.String)
        DAP.AddParameter("@ScheduleStartDateTime", EventScheduleItem.ItemStartDateTime, DbType.DateTime)
        DAP.AddParameter("@ScheduleEndDateTime", EventScheduleItem.ItemEndDateTime, DbType.DateTime)
        DAP.AddParameter("@LastUpdatedBy", Username, DbType.String)
        DAP.AddParameter("@LastUpdatedDate", Now, DbType.DateTime)
        DAP.AddParameter("@EventId", EventScheduleItem.ItemId, DbType.String)
        DAP.AddParameter("@ScheduleId", EventScheduleItem.ItemID, DbType.String)
        DAP.ExecuteNonQuery(sSQL)
    End Sub

    Private Function CBoolToYesNo(ByVal InputValue As Boolean) As String
        If InputValue = True Then
            Return "Y"
        Else
            Return "N"
        End If
    End Function

    Private Function CYesNoToBool(ByVal InputString As String) As Boolean
        If InputString.ToLower = "y" Then
            Return True
        Else
            Return False
        End If
    End Function

    Private Function GetConfigValue(ByVal configValue As String, ByVal defaultValue As String) As String
        If String.IsNullOrEmpty(configValue) Then
            Return defaultValue
        End If
        Return configValue
    End Function
End Class
